from datetime import datetime

from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash

# 创建SQLAlchemy实例
db = SQLAlchemy()


class Footprint(db.Model):
    __tablename__ = 'footprints'
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
    property_id = db.Column(db.Integer, db.ForeignKey('properties.id'), primary_key=True)
    notes = db.Column(db.Text, nullable=True)
    rating = db.Column(db.Integer, nullable=True)
    added_at = db.Column(db.DateTime(timezone=True), server_default=db.func.now())

    user = db.relationship('User', back_populates='footprints')
    property = db.relationship('Property', back_populates='followers')

    def to_dict(self):
        return {
            "notes": self.notes,
            "rating": self.rating,
            "added_at": self.added_at.isoformat(),
            "property": self.property.to_dict_summary() if self.property else None
        }
    def to_dict_for_public_review(self):
        # 为了给前端的 v-for 提供一个稳定的 :key，我们组合一个唯一的id
        # 同时，返回前端需要的所有字段：user, comment, rating, created_at
        return {
            'id': f'review-{self.user_id}-{self.property_id}',
            'user': self.user.to_dict_review() if self.user else {'username': '匿名用户'},
            'comment': self.notes, # 将 notes 映射为 comment
            'rating': self.rating,
            'created_at': self.added_at.isoformat()
        }


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.Text, nullable=False)
    role = db.Column(db.String(20), nullable=False, default='user') # 角色默认为 'user'
    apikey = db.Column(db.String(36), unique=True)
    quota = db.Column(db.Integer, default=60)
    footprints = db.relationship('Footprint', back_populates='user', cascade="all, delete-orphan")

    def set_password(self, password):
        self.password = generate_password_hash(password)

    def to_dict_review(self):
        return {
            'username': self.username
        }


class Property(db.Model):
    """【最终修正模型】根据数据库表头截图精确定义"""
    __tablename__ = 'properties'

    # 主键
    id = db.Column(db.Integer, primary_key=True) #

    # 地理与标识信息
    region = db.Column(db.String(50)) #
    business_district = db.Column(db.String(50)) #
    community = db.Column(db.String(100), nullable=False) #
    listing_year = db.Column(db.Integer) #
    listing_month = db.Column(db.Numeric(5, 2)) #

    # 价格统计信息
    unit_price_avg = db.Column(db.Float) #
    unit_price_median = db.Column(db.Float) #
    unit_price_min = db.Column(db.Float) #
    unit_price_max = db.Column(db.Float) #
    total_price_avg = db.Column(db.Float) #
    total_price_median = db.Column(db.Float) #
    total_price_min = db.Column(db.Float) #
    total_price_max = db.Column(db.Float) #

    # 坐标
    longitude = db.Column(db.Numeric(11, 8)) #
    latitude = db.Column(db.Numeric(10, 8)) #

    # 关系定义
    followers = db.relationship('Footprint', back_populates='property', cascade="all, delete-orphan")

    def to_dict(self):
        """完整的字典序列化，用于后台管理"""
        return {
            "id": self.id,
            # 键（key）是给前端API用的，值（value）必须是模型中真实存在的属性
            "name": self.community,  # self.name -> self.community
            "district": self.region,  # self.district -> self.region
            "sub_district": self.business_district,  # self.sub_district -> self.business_district
            "listing_year": self.listing_year,
            "listing_month": float(self.listing_month),  # 确保 numeric 类型可以被JSON序列化
            "coordinates": [float(self.longitude), float(self.latitude)],  # 确保坐标是float类型
            "avg_unit_price": self.unit_price_avg,  # avg_unit_price -> unit_price_avg
            "median_unit_price": self.unit_price_median,
            "min_unit_price": self.unit_price_min,
            "max_unit_price": self.unit_price_max,
            "avg_total_price": self.total_price_avg,  # avg_total_price -> total_price_avg
            "median_total_price": self.total_price_median,
            "min_total_price": self.total_price_min,
            "max_total_price": self.total_price_max,
            "longitude": float(self.longitude),
            "latitude": float(self.latitude)
        }

    def to_dict_summary(self):
        """精简的字典序列化，用于地图和足迹列表"""
        return {
            "id": self.id,
            "name": self.community,  # self.name -> self.community
            "district": self.region,  # self.district -> self.region
            "sub_district": self.business_district,  # self.sub_district -> self.business_district
            "coordinates": [float(self.longitude), float(self.latitude)],
            "avg_unit_price": self.unit_price_avg,  # self.avg_unit_price -> self.unit_price_avg
            "avg_total_price": self.total_price_avg  # self.avg_total_price -> self.total_price_avg
        }
